-- 明细导出
with site_modify as ( -- 修改线路段目的站点为发件下一站,并按单号粒度去重
    select
              shipment.shipment_no
            , shipment.shipment_name
            , shipment.billcode
            , shipment.start_site_code
            , shipment.start_site_name
            , shipment.start_site_sort_num
            , nvl(shipment.load_next_site_code,shipment.send_next_site_code) as end_site_code
            , nvl(shipment.load_next_site_name,shipment.send_next_site_name) as end_site_name
            -- , max(line.cnt)                                  as line_cnt
            , max(line.grade)                                as line_grade
            , max(shipment.vehicleline_code)                 as vehicleline_code
            , max(shipment.vehicleline_name)                 as vehicleline_name
            , max(shipment.mileage)                          as mileage
            , max(shipment.business_attribute)               as business_attribute
            , max(shipment.has_out)                          as has_out
            , max(line.strategy_line)                        as strategy_line
            , max(shipment.operation_model)                  as operation_model
            , max(shipment.plate_number)                     as plate_number
            , max(shipment.vehicletype_id)                   as vehicletype_id
            , max(shipment.vehicletype_name)                 as vehicletype_name
            , max(vehicle.axle_number)                       as axle_number
            , max(shipment.shipno_planned_departure_time)    as shipno_planned_departure_time
            , max(shipment.shipno_actual_departure_time)     as shipno_actual_departure_time
            , max(shipment.shipno_planned_arrival_time)      as shipno_planned_arrival_time
            , max(shipment.shipno_actual_arrival_time)       as shipno_actual_arrival_time
            , max(shipment.start_code)                       as start_code
            , max(shipment.start_name)                       as start_name
            , max(shipment.end_code)                         as end_code
            , max(shipment.end_name)                         as end_name
            , max(package_inside_charge_weight)              as package_inside_charge_weight
            , max(package_total_volume)                      as package_total_volume
    from jms_dwd.dwd_wide_tms_road_waybill_dt shipment
            -- 关联车线表获取时候兜底等信息
            left join jms_dim.dim_tmsnew_vehicle_line line
                        on shipment.vehicleline_id = line.id
            left join jms_dim.dim_tmsnew_vehicle vehicle
                   on shipment.plate_number = vehicle.plate_number
    where   dt >= date_sub('{{ execution_date | cst_ds }}', 2)
            and shipment.dt <= '{{ execution_date | cst_ds }}'
            and billcode is not null             -- 单号为null去除
            and substr(billcode, 1, 2) = 'JT'    -- 只要JT单号
            and shipment_state <> 5              -- 剔除作废任务号
            and is_trunk_branch = 1              -- 干线
            and nvl(shipment.load_next_site_code,shipment.send_next_site_code) is not null  -- 只取装车发件
    group by
            shipment.shipment_no
            , shipment.shipment_name
            , shipment.billcode
            , shipment.start_site_code
            , shipment.start_site_name
            , shipment.start_site_sort_num
            , nvl(shipment.load_next_site_code,shipment.send_next_site_code)
            , nvl(shipment.load_next_site_name,shipment.send_next_site_name)
),
linepart_dim as ( -- 笛卡尔积获取线路段组合的累计里程以及一些时间字段
    select
        a.shipment_no              as shipment_no
        ,a.network_code            as start_site_code
        ,a.network_name            as start_site_name
        ,a.sort_num                as start_site_sort_num
        ,a.planned_departure_time  as linepart_planned_departure_time
        ,a.actual_departure_time   as linepart_actual_departure_time
        ,b.network_code            as end_site_code
        ,b.network_name            as end_site_name
        ,b.sort_num                as end_site_sort_num
        ,b.planned_arrival_time    as linepart_planned_arrival_time
        ,b.actual_arrival_time     as linepart_actual_arrival_time
        -- ,b.acc_mileage - nvl(a.acc_mileage, 0)  as linepart_mileage -- 线路段里程(带累计)
        ,case when b.sort_num - a.sort_num = 1 then nvl(b.mileage,0)
              else 0
        end    as linepart_mileage
        ,max(b.sort_num) over(partition by a.shipment_no) as line_cnt -- 任务单途经点数量
    from (
        select
            shipment_no --任务号
            ,sort_num
            ,network_code --经停网点编码
            ,network_name --经停网点名称
            ,mileage
            ,planned_departure_time
            ,actual_departure_time
            ,planned_arrival_time
            ,actual_arrival_time
        from jms_dwd.dwd_tmsnew_shipment_stop_union_base_dt  --任务单经停点
        where dt between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
        and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
        and table_type = 1
    ) a
    left join (
            select
                shipment_no --任务号
                ,sort_num
                ,network_code --经停网点编码
                ,network_name --经停网点名称
                ,mileage
                ,planned_departure_time
                ,actual_departure_time
                ,planned_arrival_time
                ,actual_arrival_time
            from jms_dwd.dwd_tmsnew_shipment_stop_union_base_dt  --任务单经停点
            where dt between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
            and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
            and table_type = 1
    ) b on a.shipment_no = b.shipment_no
       and a.sort_num < b.sort_num
     where b.network_code is not null
),
linepart_dim_config as (
    -- 线路段关联干线维护表,如果AB段业务没有维护,取AC段维护数据
    select
        shipment_no
        ,start_site_code
        ,start_site_name
        ,start_site_sort_num
        ,end_site_code
        ,end_site_name
        ,end_site_sort_num
        ,vehicletype
        ,nvl(vehiclevolume, vehiclevolume2) as vehiclevolume
        ,nvl(vehicleweight, vehicleweight2) as vehicleweight
        ,nvl(vehiclecount,  vehiclecount2)  as vehiclecount
        ,nvl(averagevolume, averagevolume2) as averagevolume
    from (
        select
             linepart_dim.shipment_no
            ,linepart_dim.start_site_code
            ,linepart_dim.start_site_name
            ,linepart_dim.start_site_sort_num
            ,linepart_dim.end_site_code
            ,linepart_dim.end_site_name
            ,linepart_dim.end_site_sort_num
            ,config.vehicletype
            ,config.vehiclevolume
            ,config.vehicleweight
            ,config.vehiclecount
            ,config.averagevolume
            -- 线路段关联干线维护表,如果AB段业务没有维护,取AC段维护数据
            ,first_value(config.vehiclevolume,true) over(partition by linepart_dim.shipment_no, config.vehicletype, linepart_dim.start_site_code order by linepart_dim.start_site_sort_num, linepart_dim.end_site_sort_num) as vehiclevolume2
            ,first_value(config.vehicleweight,true) over(partition by linepart_dim.shipment_no, config.vehicletype, linepart_dim.start_site_code order by linepart_dim.start_site_sort_num, linepart_dim.end_site_sort_num) as vehicleweight2
            ,first_value(config.vehiclecount,true)  over(partition by linepart_dim.shipment_no, config.vehicletype, linepart_dim.start_site_code order by linepart_dim.start_site_sort_num, linepart_dim.end_site_sort_num) as vehiclecount2
            ,first_value(config.averagevolume,true) over(partition by linepart_dim.shipment_no, config.vehicletype, linepart_dim.start_site_code order by linepart_dim.start_site_sort_num, linepart_dim.end_site_sort_num) as averagevolume2
        from
            linepart_dim
        left join
                jms_dim.dim_tab_mainline_nuclear_load_dt config
                 on linepart_dim.start_site_code  = config.start_distributecode
                and linepart_dim.end_site_code    = config.end_distributecode
                and config.is_delete   = 1
                and config.dt = '{{ execution_date | cst_ds }}'
    ) a
),
linepart_count as (
    select
            shipment_no
            ,shipment_name
            -- ,line_cnt
            ,line_grade
            ,vehicleline_code
            ,vehicleline_name
            ,mileage
            ,business_attribute
            ,has_out
            ,strategy_line
            ,operation_model
            ,plate_number
            ,vehicletype_id
            ,vehicletype_name
            ,axle_number
            ,shipno_planned_departure_time
            ,shipno_actual_departure_time
            ,shipno_planned_arrival_time
            ,shipno_actual_arrival_time
            ,start_site_code
            ,start_site_name
            ,start_site_sort_num
            ,end_site_code
            ,end_site_name
            ,start_code
            ,start_name
            ,end_code
            ,end_name
            ,count(1)                                    as linepart_count
            ,sum(package_inside_charge_weight / 1000)    as linepart_weight -- 单位t
            ,sum(package_total_volume) / 1000000         as linepart_volume -- 单位转立方米
            ,sum(if(nvl(package_inside_charge_weight,0) = 0, 1, 0))                                   as weight0_count
            ,sum(if(package_inside_charge_weight > 0  and package_inside_charge_weight <=30, 1, 0))   as weight1_count
            ,sum(if(package_inside_charge_weight > 30 and package_inside_charge_weight < 1000, 1, 0)) as weight2_count
            ,sum(if(package_inside_charge_weight >= 1000, 1, 0))                                      as weight3_count
    from
        site_modify
    group by
            shipment_no
            ,shipment_name
            -- ,line_cnt
            ,line_grade
            ,vehicleline_code
            ,vehicleline_name
            ,mileage
            ,business_attribute
            ,has_out
            ,strategy_line
            ,operation_model
            ,plate_number
            ,vehicletype_id
            ,vehicletype_name
            ,axle_number
            ,shipno_planned_departure_time
            ,shipno_actual_departure_time
            ,shipno_planned_arrival_time
            ,shipno_actual_arrival_time
            ,start_site_code
            ,start_site_name
            ,start_site_sort_num
            ,end_site_code
            ,end_site_name
            ,start_code
            ,start_name
            ,end_code
            ,end_name
),
linepart_count_with_dim as (
    select
        a.shipment_no
        ,a.shipment_name
        ,linepart_dim.line_cnt
        ,a.line_grade
        ,a.vehicleline_code
        ,a.vehicleline_name
        ,a.mileage
        ,a.business_attribute
        ,a.has_out
        ,a.strategy_line
        ,a.operation_model
        ,a.plate_number
        ,a.vehicletype_id
        ,a.vehicletype_name
        ,a.axle_number
        ,a.shipno_planned_departure_time
        ,a.shipno_actual_departure_time
        ,a.shipno_planned_arrival_time
        ,a.shipno_actual_arrival_time
        ,a.start_site_code
        ,a.start_site_name
        ,a.start_site_sort_num
        ,a.end_site_code
        ,a.end_site_name
        ,a.start_code
        ,a.start_name
        ,a.end_code
        ,a.end_name
        ,a.linepart_count
        ,a.linepart_weight
        ,a.linepart_volume
        ,a.weight0_count
        ,a.weight1_count
        ,a.weight2_count
        ,a.weight3_count
        ,linepart_dim.linepart_mileage
        ,linepart_dim.linepart_planned_departure_time
        ,linepart_dim.linepart_actual_departure_time
        ,linepart_dim.linepart_planned_arrival_time
        ,linepart_dim.linepart_actual_arrival_time
        ,linepart_dim.end_site_sort_num
        ,row_number() over(partition by a.shipment_no order by a.start_site_sort_num, linepart_dim.end_site_sort_num nulls last) as rn -- 用于找到第一个线路段，注意不要取到错扫线路段（linepart_dim.end_site_sort_num is null）
    from
        linepart_count a
        left join
            linepart_dim
            on a.shipment_no      = linepart_dim.shipment_no
            and a.start_site_code = linepart_dim.start_site_code
            and a.end_site_code   = linepart_dim.end_site_code
),
linepart_count_with_dim_add as (
    -- ABC线路,如果只有AC有票数,A核还是需要用AB线路段的配置,因此需要手动造一个AB线路段
    select
        shipment_no
        ,shipment_name
        ,line_cnt
        ,line_grade
        ,vehicleline_code
        ,vehicleline_name
        ,mileage
        ,linepart_mileage
        ,business_attribute
        ,has_out
        ,strategy_line
        ,operation_model
        ,plate_number
        ,vehicletype_id
        ,vehicletype_name
        ,axle_number
        ,shipno_planned_departure_time
        ,shipno_actual_departure_time
        ,shipno_planned_arrival_time
        ,shipno_actual_arrival_time
        ,linepart_planned_departure_time
        ,linepart_actual_departure_time
        ,linepart_planned_arrival_time
        ,linepart_actual_arrival_time
        ,start_site_code
        ,start_site_name
        ,start_site_sort_num
        ,end_site_code
        ,end_site_name
        ,end_site_sort_num
        ,start_code
        ,start_name
        ,end_code
        ,end_name
        ,linepart_count
        ,linepart_weight
        ,linepart_volume
        ,weight0_count
        ,weight1_count
        ,weight2_count
        ,weight3_count
    from
        linepart_count_with_dim

    union all

    select
         a.shipment_no
        ,a.shipment_name
        ,a.line_cnt
        ,a.line_grade
        ,a.vehicleline_code
        ,a.vehicleline_name
        ,a.mileage
        ,a.linepart_mileage
        ,a.business_attribute
        ,a.has_out
        ,a.strategy_line
        ,a.operation_model
        ,a.plate_number
        ,a.vehicletype_id
        ,a.vehicletype_name
        ,a.axle_number
        ,a.shipno_planned_departure_time
        ,a.shipno_actual_departure_time
        ,a.shipno_planned_arrival_time
        ,a.shipno_actual_arrival_time
        ,a.linepart_planned_departure_time
        ,a.linepart_actual_departure_time
        ,a.linepart_planned_arrival_time
        ,a.linepart_actual_arrival_time
        ,a.start_site_code
        ,a.start_site_name
        ,a.start_site_sort_num
        ,linepart_dim.end_site_code
        ,linepart_dim.end_site_name
        ,linepart_dim.end_site_sort_num
        ,a.start_code
        ,a.start_name
        ,a.end_code
        ,a.end_name
        ,0 as linepart_count
        ,0 as linepart_weight
        ,0 as linepart_volume
        ,0 as weight0_count
        ,0 as weight1_count
        ,0 as weight2_count
        ,0 as weight3_count
    from
        linepart_count_with_dim a
            left join linepart_dim
             on a.shipment_no = linepart_dim.shipment_no
            and a.start_site_sort_num       = linepart_dim.start_site_sort_num
            and (a.start_site_sort_num + 1) = linepart_dim.end_site_sort_num
    where a.rn = 1
      and (a.end_site_sort_num - a.start_site_sort_num) > 1
),
-- 循环袋
tmp_record as (
    select shipment_no, recycle_bags_number, recycle_bags_weight
    from   (select a.shipment_no,
                   a.recycle_bags_number,
                   a.recycle_bags_weight,
                   row_number() over (partition by shipment_no order by scan_time desc ) rk
           from jms_dwd.dwd_tab_barscan_cycle_transfer_record_base_dt a
           ) x
    where x.rk = 1
)
insert overwrite table jms_tmp.dm_tms_road_waybill_linepart_sum_dt
select
    *
from(
    select
        a.shipment_no
        ,a.shipment_name
        ,a.line_cnt
        ,a.line_grade
        ,a.vehicleline_code
        ,a.vehicleline_name
        ,concat(dim_network3.simple_name, '-', dim_network4.simple_name) as linepart_name
        ,a.mileage
        ,a.linepart_mileage
        ,a.business_attribute
        ,a.has_out
        ,a.strategy_line
        ,a.operation_model
        ,a.plate_number
        ,a.vehicletype_id
        ,a.vehicletype_name
        ,a.axle_number
        ,a.shipno_planned_departure_time
        ,a.shipno_actual_departure_time
        ,a.shipno_planned_arrival_time
        ,a.shipno_actual_arrival_time
        ,a.linepart_planned_departure_time
        ,a.linepart_actual_departure_time
        ,a.linepart_planned_arrival_time
        ,a.linepart_actual_arrival_time
        ,a.start_site_code
        ,a.start_site_name
        ,a.start_site_sort_num
        ,a.end_site_code
        ,a.end_site_name
        ,a.end_site_sort_num
        ,a.start_code
        ,a.start_name
        ,a.end_code
        ,a.end_name
        ,a.linepart_count
        ,a.linepart_weight
        ,a.linepart_volume
        ,a.weight0_count
        ,a.weight1_count
        ,a.weight2_count
        ,a.weight3_count
        -- 如果A空放（也就是A不装货的情况下),再计算B的核载时，B当成A的公式进行计算。
        ,min(a.start_site_sort_num) over(partition by a.shipment_no) as min_start_site_sort_num
        ,min(a.end_site_sort_num)   over(partition by a.shipment_no) as min_end_site_sort_num
        ,cast(nvl(config1.vehiclevolume,config2.vehiclevolume) as decimal(10,5)) as vehiclevolume -- 核载体积m3
        ,cast(nvl(config1.vehicleweight,config2.vehicleweight) as decimal(10,5)) as vehicleweight -- 核载重量(单位：吨)
        ,cast(nvl(config1.vehiclecount, config2.vehiclecount ) as decimal(10,5)) as vehiclecount  -- 核载票数
        ,cast(nvl(config1.averagevolume,config2.averagevolume) as decimal(10,5)) as averagevolume -- 平均单票体积
        ,dim_network1.provider_id                                as start_province_id         -- 始发省份ID
        ,dim_network1.provider_desc                              as start_province_name       -- 始发省份名称
        ,dim_network1.regional_id                                as start_region_id           -- 始发大区ID
        ,dim_network1.regional_desc                              as start_region_name         -- 始发大区名称
        ,dim_network1.agent_code                                 as start_agent_code          -- 始发代理区ID
        ,dim_network1.agent_name                                 as start_agent_name          -- 始发代理区名称
        ,dim_network2.provider_id                                as end_province_id           -- 目的省份ID
        ,dim_network2.provider_desc                              as end_province_name         -- 目的省份名称
        ,dim_network2.regional_id                                as end_region_id             -- 目的大区ID
        ,dim_network2.regional_desc                              as end_region_name           -- 目的大区名称
        ,dim_network2.agent_code                                 as end_agent_code            -- 目的代理区ID
        ,dim_network2.agent_name                                 as end_agent_name            -- 目的代理区名称
        ,dim_network3.provider_id                                as start_site_province_id    -- 始发站点省份ID
        ,dim_network3.provider_desc                              as start_site_province_name  -- 始发站点省份名称
        ,dim_network3.regional_id                                as start_site_region_id      -- 始发站点大区ID
        ,dim_network3.regional_desc                              as start_site_region_name    -- 始发站点大区名称
        ,dim_network3.agent_code                                 as start_site_agent_code     -- 始发站点代理区ID
        ,dim_network3.agent_name                                 as start_site_agent_name     -- 始发站点代理区名称
        ,dim_network4.provider_id                                as end_site_province_id      -- 目的站点省份ID
        ,dim_network4.provider_desc                              as end_site_province_name    -- 目的站点省份名称
        ,dim_network4.regional_id                                as end_site_region_id        -- 目的站点大区ID
        ,dim_network4.regional_desc                              as end_site_region_name      -- 目的站点大区名称
        ,dim_network4.agent_code                                 as end_site_agent_code       -- 目的站点代理区ID
        ,dim_network4.agent_name                                 as end_site_agent_name       -- 目的站点代理区名称
        , case
            when a.line_cnt = 2 and a.line_grade = 4 then 1 --短驳
            when a.line_cnt = 2 then 2 -- 直发
            when a.line_cnt > 2 then 3 -- 经停
        end                                           as line_type
        , sum(if(a.end_site_sort_num is null, a.linepart_count, 0)) over(partition by a.shipment_no) as error_scan_count -- 错扫票数(装车发件下一站不在任务单经停点内)
        -- , if(linepart_dim.shipment_no is null, 1, 0)                                                      as error_scan_flag
        , nvl(tmp_record.recycle_bags_number, 0)                                                 as cycle_bag_cnt       -- 循环袋数
        , nvl(tmp_record.recycle_bags_weight, 0)                                                 as cycle_bag_weight    -- 循环袋重量
        , case
              when nvl(a.linepart_volume, 0) = 0
                  and nvl(a.linepart_count, 0) = 0
                  and nvl(tmp_record.recycle_bags_number, 0) >= 0
                  and nvl(tmp_record.recycle_bags_weight, 0) >= 0
                  then 1
              else 0 end                                                                         as issupplies          -- 是否物资线路
    from
        linepart_count_with_dim_add a
        left join
            linepart_dim_config config1
             on a.start_site_code  = config1.start_site_code
            and a.end_site_code    = config1.end_site_code
            and a.vehicletype_name = config1.vehicletype
            and a.shipment_no      = config1.shipment_no
        left join
            jms_dim.dim_tab_mainline_nuclear_load_dt config2
             on a.vehicletype_name = config2.vehicletype
            and config2.start_distributecode is null
            and config2.end_distributecode is null
            and config2.is_delete = 1
            and config2.dt = '{{ execution_date | cst_ds }}'
        left join
            jms_dim.dim_network_whole_massage dim_network1
                on a.start_code = dim_network1.code
        left join
            jms_dim.dim_network_whole_massage dim_network2
                on a.end_code = dim_network2.code
        left join
            jms_dim.dim_network_whole_massage dim_network3
                on a.start_site_code = dim_network3.code
        left join
            jms_dim.dim_network_whole_massage dim_network4
                on a.end_site_code = dim_network4.code
        left join tmp_record
          on a.shipment_no = tmp_record.shipment_no
) a
where end_site_sort_num is not null -- 剔除错扫线路
distribute by 1
;





with acc_load as (
    select
        shipment_no
        ,sort_num
        ,site_code
        ,site_name
        ,min_start_site_sort_num
        ,min_end_site_sort_num
        ,sum(load_cnt)       as load_cnt
        ,sum(sum(load_cnt)) over(partition by shipment_no order by sort_num)   as acc_load_cnt
        ,sum(unload_cnt)     as unload_cnt
        ,sum(sum(unload_cnt)) over(partition by shipment_no order by sort_num) as acc_unload_cnt

        ,sum(load_weight)       as load_weight
        ,sum(sum(load_weight)) over(partition by shipment_no order by sort_num)   as acc_load_weight
        ,sum(unload_weight)     as unload_weight
        ,sum(sum(unload_weight)) over(partition by shipment_no order by sort_num) as acc_unload_weight

        ,sum(load_volume)       as load_volume
        ,sum(sum(load_volume)) over(partition by shipment_no order by sort_num)   as acc_load_volume
        ,sum(unload_volume)     as unload_volume
        ,sum(sum(unload_volume)) over(partition by shipment_no order by sort_num) as acc_unload_volume
    from(
        select
            shipment_no
            , start_site_sort_num    as sort_num
            , start_site_code        as site_code
            , start_site_name        as site_name
            , min_start_site_sort_num
            , min_end_site_sort_num
            , sum(linepart_count)    as load_cnt
            , 0                      as unload_cnt
            , sum(linepart_weight)   as load_weight
            , 0                      as unload_weight
            , sum(linepart_volume)   as load_volume
            , 0                      as unload_volume
        from
            jms_tmp.dm_tms_road_waybill_linepart_sum_dt
        group by
            shipment_no
            , start_site_sort_num
            , start_site_code
            , start_site_name
            , min_start_site_sort_num
            , min_end_site_sort_num

        union all

        select
            shipment_no
            , end_site_sort_num
            , end_site_code
            , end_site_name
            , min_start_site_sort_num
            , min_end_site_sort_num
            , 0
            , sum(linepart_count)    as unload_cnt
            , 0
            , sum(linepart_weight)   as unload_weight
            , 0
            , sum(linepart_volume)   as unload_volume

        from
            jms_tmp.dm_tms_road_waybill_linepart_sum_dt
        group by
            shipment_no
            , end_site_sort_num
            , end_site_code
            , end_site_name
            , min_start_site_sort_num
            , min_end_site_sort_num
    ) a
    group by
        shipment_no
        ,sort_num
        ,site_code
        ,site_name
        , min_start_site_sort_num
        , min_end_site_sort_num
)
-- 首线路段核载
,linepart_plan as (
    select
        shipment_no
        ,round(nvl(first_linepart_plan_volume / first_linepart_avg_volume,0))                        as plan_linepart_count  -- 核载票数
        ,cast(nvl(first_linepart_plan_volume,0) as decimal(10,5))                                    as plan_linepart_volume -- 核载体积
        ,cast(nvl(first_linepart_plan_weight,0) as decimal(10,5))                                    as plan_linepart_weight -- 核载重量
    from(
        select
            a.shipment_no
            ,sum(case when
                    a.start_site_sort_num = a.min_start_site_sort_num and a.end_site_sort_num = a.min_end_site_sort_num then a.vehiclevolume
                end
            )                                                                          as first_linepart_plan_volume -- 首线路段核载体积
            ,sum(case when
                    a.start_site_sort_num = a.min_start_site_sort_num and a.end_site_sort_num = a.min_end_site_sort_num then a.vehicleweight
                end
            )                                                                          as first_linepart_plan_weight -- 首线路段核载体积
            ,double(sum(a.linepart_count * a.averagevolume) / sum(a.linepart_count))   as first_linepart_avg_volume  -- 首线路段平均单票体积
        from
            jms_tmp.dm_tms_road_waybill_linepart_sum_dt a
        group by
            a.shipment_no
    ) a
)
,load_rate as (
    select
        acc_load.shipment_no
        ,acc_load.sort_num
        ,acc_load.site_code
        ,acc_load.site_name
        ,acc_load.load_cnt
        ,acc_load.acc_load_cnt
        ,acc_load.unload_cnt
        ,acc_load.acc_unload_cnt
        -- linepart_plan.plan_linepart_count: 首站线路段核载票数
        -- (acc_load.acc_load_cnt - acc_load.load_cnt)：上一站累计装载票数,不包含本站装载
        -- acc_load.acc_unload_cnt：累计卸车票数，包含本站卸车
        ,case when acc_load.sort_num = acc_load.min_start_site_sort_num  then linepart_plan.plan_linepart_count
              else linepart_plan.plan_linepart_count - (acc_load.acc_load_cnt - acc_load.load_cnt) + acc_load.acc_unload_cnt
         end       as plan_count -- 核载票数
        ,case when  acc_load.sort_num = acc_load.min_start_site_sort_num then linepart_plan.plan_linepart_volume
              else linepart_plan.plan_linepart_volume - (acc_load.acc_load_volume - acc_load.load_volume) + acc_load.acc_unload_volume
        end        as plan_volume

        ,case when  acc_load.sort_num = acc_load.min_start_site_sort_num then linepart_plan.plan_linepart_weight
              else linepart_plan.plan_linepart_weight - (acc_load.acc_load_weight - acc_load.load_weight) + acc_load.acc_unload_weight
        end        as plan_weight
        ,max(acc_load_cnt - acc_unload_cnt)       over(partition by acc_load.shipment_no)  as line_count                      -- 最大线路段实际装载量
        ,max(acc_load_volume - acc_unload_volume) over(partition by acc_load.shipment_no)  as line_volume                     -- 最大线路段实际装载量
        ,max(acc_load_weight - acc_unload_weight) over(partition by acc_load.shipment_no)  as line_weight                     -- 最大线路段实际装载量
        ,max(linepart_plan.plan_linepart_count)   over(partition by acc_load.shipment_no)  as first_site_plan_linepart_count  -- 首线路段核载票数
        ,max(linepart_plan.plan_linepart_volume)  over(partition by acc_load.shipment_no)  as first_site_plan_linepart_volume -- 首线路段核载票数
        ,max(linepart_plan.plan_linepart_weight)  over(partition by acc_load.shipment_no)  as first_site_plan_linepart_weight -- 首线路段核载票数
    from
        acc_load
        left join linepart_plan on acc_load.shipment_no = linepart_plan.shipment_no
    )
insert overwrite table jms_dm.dm_tms_shipment_line_load_rate_dt partition (dt)
select
     a.shipment_no
    ,a.shipment_name
    ,to_date(a.shipno_planned_departure_time)    as date_time
    ,a.vehicleline_code
    ,a.vehicleline_name
    ,a.linepart_name
    ,a.mileage
    ,a.linepart_mileage
    ,a.business_attribute
    ,a.has_out
    ,a.strategy_line
    ,a.operation_model
    ,a.plate_number
    ,a.vehicletype_id
    ,a.vehicletype_name
    ,a.axle_number
    ,a.shipno_planned_departure_time
    ,a.shipno_actual_departure_time
    ,a.shipno_planned_arrival_time
    ,a.shipno_actual_arrival_time
    ,a.linepart_planned_departure_time
    ,a.linepart_actual_departure_time
    ,a.linepart_planned_arrival_time
    ,a.linepart_actual_arrival_time
    ,a.start_site_code
    ,a.start_site_name
    ,a.start_site_sort_num
    ,a.end_site_code
    ,a.end_site_name
    ,a.end_site_sort_num
    ,a.start_code
    ,a.start_name
    ,a.end_code
    ,a.end_name

    ,a.linepart_count             -- 线路段装载票数
    ,greatest(b.plan_count,0)     -- 线路段核载票数
    ,round(greatest(least(sum(a.linepart_count) over(partition by a.shipment_no, a.start_site_code) / b.plan_count * 100,100),0), 2)              as linepart_rate
    ,b.line_count                                     -- 线路最大装载票数
    ,greatest(b.first_site_plan_linepart_count, 0)    -- 线路核载票数
    ,round(greatest(least(b.line_count / b.first_site_plan_linepart_count * 100,100),0),2)   as line_rate

    ,a.linepart_weight * 1000                -- 线路段装载重量
    ,greatest(b.plan_weight, 0) * 1000       -- 线路段核载重量
    ,round(greatest(least(sum(a.linepart_weight) over(partition by a.shipment_no, a.start_site_code) / b.plan_weight * 100,100),0), 2)           as linepart_weight_rate
    ,b.line_weight              * 1000                     -- 线路最大装载重量
    ,greatest(b.first_site_plan_linepart_weight, 0) * 1000    -- 线路核载重量
    ,round(greatest(least(b.line_weight / b.first_site_plan_linepart_weight * 100,100),0),2)   as line_weight_rate



    ,a.linepart_volume              -- 线路段装载体积
    ,greatest(b.plan_volume, 0)     -- 线路段核载体积
    ,round(greatest(least(sum(a.linepart_volume) over(partition by a.shipment_no, a.start_site_code) / b.plan_volume * 100,100),0), 2)           as linepart_volume_rate
    ,b.line_volume                                      -- 线路最大装载体积
    ,greatest(b.first_site_plan_linepart_volume, 0)     -- 线路核载体积
    ,round(greatest(least(b.line_volume / b.first_site_plan_linepart_volume * 100,100),0),2)   as line_weight_rate

    ,a.start_province_id         -- 始发省份ID
    ,a.start_province_name       -- 始发省份名称
    ,a.start_region_id           -- 始发大区ID
    ,a.start_region_name         -- 始发大区名称
    ,a.start_agent_code          -- 始发代理区ID
    ,a.start_agent_name          -- 始发代理区名称
    ,a.end_province_id           -- 目的省份ID
    ,a.end_province_name         -- 目的省份名称
    ,a.end_region_id             -- 目的大区ID
    ,a.end_region_name           -- 目的大区名称
    ,a.end_agent_code            -- 目的代理区ID
    ,a.end_agent_name            -- 目的代理区名称
    ,a.start_site_province_id    -- 始发站点省份ID
    ,a.start_site_province_name  -- 始发站点省份名称
    ,a.start_site_region_id      -- 始发站点大区ID
    ,a.start_site_region_name    -- 始发站点大区名称
    ,a.start_site_agent_code     -- 始发站点代理区ID
    ,a.start_site_agent_name     -- 始发站点代理区名称
    ,a.end_site_province_id      -- 目的站点省份ID
    ,a.end_site_province_name    -- 目的站点省份名称
    ,a.end_site_region_id        -- 目的站点大区ID
    ,a.end_site_region_name      -- 目的站点大区名称
    ,a.end_site_agent_code       -- 目的站点代理区ID
    ,a.end_site_agent_name       -- 目的站点代理区名称+
    ,a.weight0_count             -- 重量段0数量(w=0kg)
    ,a.weight1_count             -- 重量段1数量(0<w<=30kg)
    ,a.weight2_count             -- 重量段2数量(30<w<1000kg)
    ,a.weight3_count             -- 重量段3数量(w>=1000kg)
    ,a.line_type                 -- 线路类型1短驳2直发3经停
    ,case when a.error_scan_count > 0 then 1 else 0 end as error_scan_flag
    ,a.error_scan_count          -- 错扫票数
    ,a.cycle_bag_cnt             -- 循环袋数量
    ,a.cycle_bag_weight          -- 循环袋重量
    ,a.issupplies                -- 是否物资线路
    -- 应装循环袋数量=（线路首发中心对应的核载重量-线路实际装载重量）/0.5kg 。   （商取整数，比如0.2，那就计为1票。14.5就计为15票）
     , if(first_value(a.vehicleweight) over (partition by a.shipment_no order by a.start_site_sort_num ) > b.line_weight,
          ceiling((first_value(a.vehicleweight) over (partition by a.shipment_no order by a.start_site_sort_num ) -
                   b.line_weight) * 1000 / 0.5)
        , 0)                                         as should_cycle_bag_cnt    -- 应装循环袋数
    ,substr(a.shipno_planned_departure_time,1,10)    as dt
from
    jms_tmp.dm_tms_road_waybill_linepart_sum_dt a
    left join
        load_rate b
        on a.shipment_no = b.shipment_no
        and a.start_site_code = b.site_code
    where a.linepart_count > 0 -- 剔除造出来的线路
distribute by 1
;
